<?php

class multitender_model_graph extends multitender_model {
    const WEEKS = 20;
    const MONTHS = 12;

    function  __construct() {
        parent::__construct();
        $this->db = & $this->conf['dbs']['person'];
        $this->db_tenders = & $this->conf['dbs']['tenders'];
    }

    public function getNewPayments2($from, $to, $group, $from_cmp = 0, $to_cmp = 0) {
        $result = array();
        
        $from = (int) $from;
        $to = (int) $to;
        $from_cmp = (int) $from_cmp;
        $to_cmp = (int) $to_cmp;
        
        switch ($group) {
            case 1:
                $group_by_format = '%d.%m.%Y'; break;
            case 2:
                $group_by_format = '%v.%x'; break;
            case 3:
                $group_by_format = '%m.%Y'; break;
        }
        
        $sql = "SELECT DATE_FORMAT(`datetime_adopted`, '$group_by_format') AS `period`, COUNT(*) AS `cnt`, `datetime_adopted` 
                FROM `payments`
                WHERE `paid` = 1 AND `datetime_adopted` BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                GROUP BY `period`
                ORDER BY `datetime_adopted`;";
        //$result[] = $this->db->GetArray($sql);
        $result[] = $this->fill_array($this->db->GetArray($sql), $from, $to, $group);
        //`datetime_adopted` BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
        //AND `datetime_adopted` BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
        
        $sql = "SELECT DATE_FORMAT(`datetime_adopted`, '$group_by_format') AS `period`, COUNT(*) AS `count`, `datetime_adopted` 
                FROM (
                    SELECT `datetime_adopted`, COUNT(*) AS cnt 
                    FROM `payments` 
                    WHERE paid = 1 AND `datetime_adopted` BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                    GROUP BY user_id 
                    HAVING cnt = 1) AS `sm`
                GROUP BY `period`
                ORDER BY `datetime_adopted`;";
        //$result[] = $this->db->GetArray($sql);
        $result[] = $this->fill_array($this->db->GetArray($sql), $from, $to, $group);
        
        if ($from_cmp != 0 && $to_cmp != 0) {
            $sql = "SELECT DATE_FORMAT(`datetime_adopted`, '$group_by_format') AS `period`, COUNT(*) AS `cnt`, `datetime_adopted` 
                    FROM `payments`
                    WHERE `paid` = 1 AND `datetime_adopted` BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    GROUP BY `period`
                    ORDER BY `datetime_adopted`;";
            //$result[] = $this->db->GetArray($sql);
            $result[] = $this->fill_array($this->db->GetArray($sql), $from_cmp, $to_cmp, $group);

            $sql = "SELECT DATE_FORMAT(`datetime_adopted`, '$group_by_format') AS `period`, COUNT(*) AS `count`, `datetime_adopted` 
                    FROM (
                        SELECT `datetime_adopted`, COUNT(*) AS cnt 
                        FROM `payments` 
                        WHERE paid = 1 AND `datetime_adopted` BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                        GROUP BY user_id 
                        HAVING cnt = 1) AS `sm`
                    GROUP BY `period`
                    ORDER BY `datetime_adopted`;";
            //$result[] = $this->db->GetArray($sql);
            $result[] = $this->fill_array($this->db->GetArray($sql), $from_cmp, $to_cmp, $group);
        }
        
        if ($this->check_array($result)) {
            if ($from_cmp != 0 && $to_cmp != 0) {
                $result = $this->clear_array($result, 2);
            } else {
                $result = $this->clear_array($result);
            }
            return $result;
        } else {
            return null;
        }
    }
    
    public function getMoney2($from, $to, $group, $from_cmp = 0, $to_cmp = 0) {
        $result = array();
        
        $from = (int) $from;
        $to = (int) $to;
        $from_cmp = (int) $from_cmp;
        $to_cmp = (int) $to_cmp;
        
        switch ($group) {
            case 1:
                $group_by_format = '%d.%m.%Y'; break;
            case 2:
                $group_by_format = '%v.%x'; break;
            case 3:
                $group_by_format = '%m.%Y'; break;
        }
        
        $sql = "SELECT DATE_FORMAT(`datetime_adopted`, '$group_by_format') AS `period`, ROUND(SUM(summa)) as summ, datetime_adopted
                FROM payments
                WHERE paid = 1 AND `datetime_adopted` BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                GROUP BY period
                ORDER BY datetime_adopted;";
        $result[] = $this->fill_array($this->db->GetArray($sql), $from, $to, $group);
        
        $sql = "SELECT DATE_FORMAT(`datetime_adopted`, '$group_by_format') AS `period`, ROUND(SUM(summa)) as summ, datetime_adopted
                FROM payments
                WHERE paid = 1 AND type_payment = 'cashless' AND `datetime_adopted` BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                GROUP BY period
                ORDER BY datetime_adopted;";
        $result[] = $this->fill_array($this->db->GetArray($sql), $from, $to, $group);
        
        $sql = "SELECT DATE_FORMAT(`datetime_adopted`, '$group_by_format') AS `period`, ROUND(SUM(summa)) as summ, datetime_adopted
                FROM payments
                WHERE paid = 1 AND type_payment = 'electron' AND `datetime_adopted` BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                GROUP BY period
                ORDER BY datetime_adopted;";
        $result[] = $this->fill_array($this->db->GetArray($sql), $from, $to, $group);
        
        if ($from_cmp != 0 && $to_cmp != 0) {
            $sql = "SELECT DATE_FORMAT(`datetime_adopted`, '$group_by_format') AS `period`, ROUND(SUM(summa)) as summ, datetime_adopted
                    FROM payments
                    WHERE paid = 1 AND `datetime_adopted` BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    GROUP BY period
                    ORDER BY datetime_adopted;";
            $result[] = $this->fill_array($this->db->GetArray($sql), $from_cmp, $to_cmp, $group);

            $sql = "SELECT DATE_FORMAT(`datetime_adopted`, '$group_by_format') AS `period`, ROUND(SUM(summa)) as summ, datetime_adopted
                    FROM payments
                    WHERE paid = 1 AND type_payment = 'cashless' AND `datetime_adopted` BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    GROUP BY period
                    ORDER BY datetime_adopted;";
            $result[] = $this->fill_array($this->db->GetArray($sql), $from_cmp, $to_cmp, $group);

            $sql = "SELECT DATE_FORMAT(`datetime_adopted`, '$group_by_format') AS `period`, ROUND(SUM(summa)) as summ, datetime_adopted
                    FROM payments
                    WHERE paid = 1 AND type_payment = 'electron' AND `datetime_adopted` BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    GROUP BY period
                    ORDER BY datetime_adopted;";
            $result[] = $this->fill_array($this->db->GetArray($sql), $from_cmp, $to_cmp, $group);
        }
        
        if ($this->check_array($result)) {
            if ($from_cmp != 0 && $to_cmp != 0) {
                $result = $this->clear_array($result, 3);
            } else {
                $result = $this->clear_array($result);
            }
            return $result;
        } else {
            return null;
        }
    }
    
    public function getNewRegs2($from, $to, $group, $from_cmp = 0, $to_cmp = 0) {
        $result = array();
        
        $from = (int) $from;
        $to = (int) $to;
        $from_cmp = (int) $from_cmp;
        $to_cmp = (int) $to_cmp;
        
        switch ($group) {
            case 1:
                $group_by_format = '%d.%m.%Y'; break;
            case 2:
                $group_by_format = '%v.%x'; break;
            case 3:
                $group_by_format = '%m.%Y'; break;
        }
        
        $sql = "SELECT DATE_FORMAT(`timestamp`, '$group_by_format') AS `period`, COUNT(*) as cnt, timestamp
                FROM users
                WHERE timestamp BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                GROUP BY period
                ORDER BY timestamp";
        //$result[] = $this->db->GetArray($sql);
        $result[] = $this->fill_array($this->db->GetArray($sql), $from, $to, $group);
        
        $sql = "SELECT DATE_FORMAT(`datetime`, '$group_by_format') AS `period`, COUNT(*) AS `cnt`, `datetime` 
                FROM `payments`
                WHERE `datetime` BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                GROUP BY `period`
                ORDER BY `datetime`;";
        //$result[] = $this->db->GetArray($sql);
        $result[] = $this->fill_array($this->db->GetArray($sql), $from, $to, $group);
        
        if ($from_cmp != 0 && $to_cmp != 0) {
            $sql = "SELECT DATE_FORMAT(`timestamp`, '$group_by_format') AS `period`, COUNT(*) as cnt, timestamp
                    FROM users
                    WHERE timestamp BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    GROUP BY period
                    ORDER BY timestamp";
            //$result[] = $this->db->GetArray($sql);
            $result[] = $this->fill_array($this->db->GetArray($sql), $from_cmp, $to_cmp, $group);
            
            $sql = "SELECT DATE_FORMAT(`datetime`, '$group_by_format') AS `period`, COUNT(*) AS `cnt`, `datetime` 
                    FROM `payments`
                    WHERE `datetime` BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    GROUP BY `period`
                    ORDER BY `datetime`;";
            //$result[] = $this->db->GetArray($sql);
            $result[] = $this->fill_array($this->db->GetArray($sql), $from_cmp, $to_cmp, $group);
        }
        
        if ($this->check_array($result)) {
            if ($from_cmp != 0 && $to_cmp != 0) {
                $result = $this->clear_array($result, 2);
            } else {
                $result = $this->clear_array($result);
            }
            return $result;
        } else {
            return null;
        }
    }
    
    public function getNewTenders2($from, $to, $group, $from_cmp = 0, $to_cmp = 0) {
        $result = array();
        
        $from = (int) $from;
        $to = (int) $to;
        $from_cmp = (int) $from_cmp;
        $to_cmp = (int) $to_cmp;
        
        switch ($group) {
            case 1:
                $group_by_format = '%d.%m.%Y'; break;
            case 2:
                $group_by_format = '%v.%x'; break;
            case 3:
                $group_by_format = '%m.%Y'; break;
        }
        
        $sql = "SELECT DATE_FORMAT(`date`, '$group_by_format') AS `period`, COUNT(*) as cnt, date
                FROM item
                WHERE sector_id = 1 AND date BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                GROUP BY period
                ORDER BY date";
        //$result[1] = $this->db_tenders->GetArray($sql);
        $result[1] = $this->fill_array($this->db_tenders->GetArray($sql), $from, $to, $group);
        
        $sql = "SELECT DATE_FORMAT(`date`, '$group_by_format') AS `period`, COUNT(*) as cnt, date
                FROM item
                WHERE sector_id = 2 AND date BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                GROUP BY period
                ORDER BY date";
        //$result[2] = $this->db_tenders->GetArray($sql);
        $result[2] = $this->fill_array($this->db_tenders->GetArray($sql), $from, $to, $group);
        
        foreach ($result[1] as $res_id => $res) {
            $result[0][$res_id]['0'] = $result[1][$res_id]['0'];
            $result[0][$res_id]['2'] = $result[1][$res_id]['2'];
            $result[0][$res_id]['1'] = $result[1][$res_id]['1'] + $result[2][$res_id]['1'];
        }
        
        /*$sql = "SELECT DATE_FORMAT(`date`, '$group_by_format') AS `period`, COUNT(*) as cnt, date
                FROM item
                GROUP BY period
                HAVING date BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                ORDER BY date";
        $result[] = $this->db_tenders->GetArray($sql);*/
        
        if ($from_cmp != 0 && $to_cmp != 0) {
            /*$sql = "SELECT DATE_FORMAT(`date`, '$group_by_format') AS `period`, COUNT(*) as cnt, date
                    FROM item
                    GROUP BY period
                    HAVING date BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    ORDER BY date";
            $result[] = $this->db_tenders->GetArray($sql);*/
            
            $sql = "SELECT DATE_FORMAT(`date`, '$group_by_format') AS `period`, COUNT(*) as cnt, date
                    FROM item
                    WHERE sector_id = 1 AND date BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    GROUP BY period
                    ORDER BY date";
            //$result[4] = $this->db_tenders->GetArray($sql);
            $result[4] = $this->fill_array($this->db_tenders->GetArray($sql), $from_cmp, $to_cmp, $group);

            $sql = "SELECT DATE_FORMAT(`date`, '$group_by_format') AS `period`, COUNT(*) as cnt, date
                    FROM item
                    WHERE sector_id = 2 AND date BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    GROUP BY period
                    ORDER BY date";
            //$result[5] = $this->db_tenders->GetArray($sql);
            $result[5] = $this->fill_array($this->db_tenders->GetArray($sql), $from_cmp, $to_cmp, $group);
            
            foreach ($result[4] as $res_id => $res) {
                $result[3][$res_id]['0'] = $result[4][$res_id]['0'];
                $result[3][$res_id]['2'] = $result[4][$res_id]['2'];
                $result[3][$res_id]['1'] = $result[4][$res_id]['1'] + $result[5][$res_id]['1'];
            }
        }
        
        ksort($result);
        
        if ($this->check_array($result)) {
            if ($from_cmp != 0 && $to_cmp != 0) {
                $result = $this->clear_array($result, 3);
            } else {
                $result = $this->clear_array($result);
            }
            return $result;
        } else {
            return null;
        }
    }
    
    public function getNewModerator2($from, $to, $group, $from_cmp = 0, $to_cmp = 0) {
        $result = array();
        
        $from = (int) $from;
        $to = (int) $to;
        $from_cmp = (int) $from_cmp;
        $to_cmp = (int) $to_cmp;
        
        switch ($group) {
            case 1:
                $group_by_format = '%d.%m.%Y'; break;
            case 2:
                $group_by_format = '%v.%x'; break;
            case 3:
                $group_by_format = '%m.%Y'; break;
        }
        
        $sql = "SELECT DATE_FORMAT(`date`, '$group_by_format') AS `period`, COUNT(*) as cnt, date
                FROM item
                WHERE site_id=990001 AND date BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                GROUP BY period
                ORDER BY date";
        //$result[] = $this->db_tenders->GetArray($sql);
        $result[] = $this->fill_array($this->db_tenders->GetArray($sql), $from, $to, $group);
        
        if ($from_cmp != 0 && $to_cmp != 0) {
            $sql = "SELECT DATE_FORMAT(`date`, '$group_by_format') AS `period`, COUNT(*) as cnt, date
                    FROM item
                    WHERE site_id=990001 AND date BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                    GROUP BY period
                    ORDER BY date";
            //$result[] = $this->db_tenders->GetArray($sql);
            $result[] = $this->fill_array($this->db_tenders->GetArray($sql), $from_cmp, $to_cmp, $group);
        }
        
        if ($this->check_array($result)) {
            if ($from_cmp != 0 && $to_cmp != 0) {
                $result = $this->clear_array($result, 1);
            } else {
                $result = $this->clear_array($result);
            }
            return $result;
        } else {
            return null;
        }
    }
    
    public function getPayedNonpayed2($from, $to, $group, $from_cmp = 0, $to_cmp = 0) {
        $result = array();
        
        $from = (int) $from;
        $to = (int) $to;
        $from_cmp = (int) $from_cmp;
        $to_cmp = (int) $to_cmp;
        
        switch ($group) {
            case 1:
                $group_by_format = '%d.%m.%Y'; break;
            case 2:
                $group_by_format = '%v.%x'; break;
            case 3:
                $group_by_format = '%m.%Y'; break;
        }
        
        // Payed
        $sql = "SELECT DATE_FORMAT(`datetime`, '$group_by_format') AS `period`, COUNT(*) AS `cnt`, `datetime` 
                FROM `payments`
                WHERE `paid` = 1 AND `datetime` BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                GROUP BY `period`
                ORDER BY `datetime`;";
        //$result[] = $this->db->GetArray($sql);
        $result[] = $this->fill_array($this->db->GetArray($sql), $from, $to, $group);
        
        // Not payed
        $sql = "SELECT DATE_FORMAT(`datetime`, '$group_by_format') AS `period`, COUNT(*) AS `cnt`, `datetime` 
                FROM `payments`
                WHERE `paid` = 0 AND `datetime` BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                GROUP BY `period`
                ORDER BY `datetime`;";
        //$result[] = $this->db->GetArray($sql);
        $result[] = $this->fill_array($this->db->GetArray($sql), $from, $to, $group);
        
        if ($from_cmp != 0 && $to_cmp != 0) {
            // Payed
            $sql = "SELECT DATE_FORMAT(`datetime`, '$group_by_format') AS `period`, COUNT(*) AS `cnt`, `datetime` 
                    FROM `payments`
                    WHERE `paid` = 1 AND `datetime` BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    GROUP BY `period`
                    ORDER BY `datetime`;";
            //$result[] = $this->db->GetArray($sql);
            $result[] = $this->fill_array($this->db->GetArray($sql), $from_cmp, $to_cmp, $group);

            // Not payed
            $sql = "SELECT DATE_FORMAT(`datetime`, '$group_by_format') AS `period`, COUNT(*) AS `cnt`, `datetime` 
                    FROM `payments`
                    WHERE `paid` = 0 AND `datetime` BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    GROUP BY `period`
                    ORDER BY `datetime`;";
            //$result[] = $this->db->GetArray($sql);
            $result[] = $this->fill_array($this->db->GetArray($sql), $from_cmp, $to_cmp, $group);
        }
        
        if ($this->check_array($result)) {
            if ($from_cmp != 0 && $to_cmp != 0) {
                $result = $this->clear_array($result, 2);
            } else {
                $result = $this->clear_array($result);
            }
            return $result;
        } else {
            return null;
        }
    }
    
    public function getCommercial2($from, $to, $group, $from_cmp = 0, $to_cmp = 0) {
        $result = array();
        
        $from = (int) $from;
        $to = (int) $to;
        $from_cmp = (int) $from_cmp;
        $to_cmp = (int) $to_cmp;
        
        switch ($group) {
            case 1:
                $group_by_format = '%d.%m.%Y'; break;
            case 2:
                $group_by_format = '%v.%x'; break;
            case 3:
                $group_by_format = '%m.%Y'; break;
        }
        
        $sql = "SELECT DATE_FORMAT(`date`, '$group_by_format') AS `period`, COUNT(*) as cnt, date
                FROM item
                WHERE sector_id = 1
                GROUP BY period
                HAVING date BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                ORDER BY date";
        $result[] = $this->db_tenders->GetArray($sql);
        
        $sql = "SELECT DATE_FORMAT(`date`, '$group_by_format') AS `period`, COUNT(*) as cnt, date
                FROM item
                WHERE sector_id = 2
                GROUP BY period
                HAVING date BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                ORDER BY date";
        $result[] = $this->db_tenders->GetArray($sql);
        
        if ($from_cmp != 0 && $to_cmp != 0) {
            $sql = "SELECT DATE_FORMAT(`date`, '$group_by_format') AS `period`, COUNT(*) as cnt, date
                    FROM item
                    WHERE sector_id = 1
                    GROUP BY period
                    HAVING date BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    ORDER BY date";
            $result[] = $this->db_tenders->GetArray($sql);

            $sql = "SELECT DATE_FORMAT(`date`, '$group_by_format') AS `period`, COUNT(*) as cnt, date
                    FROM item
                    WHERE sector_id = 2
                    GROUP BY period
                    HAVING date BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    ORDER BY date";
            $result[] = $this->db_tenders->GetArray($sql);
        }
        
        if ($this->check_array($result)) {
            if ($from_cmp != 0 && $to_cmp != 0) {
                $result = $this->clear_array($result, 2);
            } else {
                $result = $this->clear_array($result);
            }
            return $result;
        } else {
            return null;
        }
    }
    
    public function getEnding2($from, $to, $group, $from_cmp = 0, $to_cmp = 0) {
        $result = array();
        
        $from = (int) $from;
        $to = (int) $to;
        $from_cmp = (int) $from_cmp;
        $to_cmp = (int) $to_cmp;
        
        switch ($group) {
            case 1:
                $group_by_format = '%d.%m.%Y'; break;
            case 2:
                $group_by_format = '%v.%x'; break;
            case 3:
                $group_by_format = '%m.%Y'; break;
        }
        
        $sql = "SELECT DATE_FORMAT(`todate`, '$group_by_format') AS `period`, COUNT(*) AS `cnt`, `todate` 
                FROM `access`
                WHERE `tenders` = 0 AND `todate` BETWEEN FROM_UNIXTIME($from) AND FROM_UNIXTIME($to)
                GROUP BY `period`
                ORDER BY `todate`;";
        //$result[] = $this->db->GetArray($sql);
        $result[] = $this->fill_array($this->db->GetArray($sql), $from, $to, $group);
        
        if ($from_cmp != 0 && $to_cmp != 0) {
            $sql = "SELECT DATE_FORMAT(`todate`, '$group_by_format') AS `period`, COUNT(*) AS `cnt`, `todate` 
                    FROM `access`
                    WHERE `tenders` = 0 AND `todate` BETWEEN FROM_UNIXTIME($from_cmp) AND FROM_UNIXTIME($to_cmp)
                    GROUP BY `period`
                    ORDER BY `todate`;";
            //$result[] = $this->db->GetArray($sql);
            $result[] = $this->fill_array($this->db->GetArray($sql), $from_cmp, $to_cmp, $group);
        }
        
        if ($this->check_array($result)) {
            if ($from_cmp != 0 && $to_cmp != 0) {
                $result = $this->clear_array($result, 1);
            } else {
                $result = $this->clear_array($result);
            }
            return $result;
        } else {
            return null;
        }
    }
    
    public function getNewPayments($type, $request=0) {
        $result = array();
        switch ($type) {
            case 'year': {
                $sql = "SELECT CONCAT(YEAR(datetime_adopted), '-', MONTH(datetime_adopted)) as period, COUNT(*) as cnt, datetime_adopted
                        FROM (
                            SELECT datetime_adopted, COUNT(*) as cnt
                            FROM payments
                            WHERE paid=1
                            GROUP BY user_id
                            HAVING cnt=1
                        ) as smth
                        GROUP BY period
                        HAVING DATE(datetime_adopted) > DATE(DATE_SUB(CURDATE(), INTERVAL 12 MONTH))
                        ORDER BY YEAR(datetime_adopted) ASC, MONTH(datetime_adopted) ASC";
                $result[] = $this->db->GetArray($sql);
                $sql = "SELECT CONCAT(YEAR(datetime_adopted), '-', MONTH(datetime_adopted)) as period, COUNT(*) as cnt, datetime_adopted
                        FROM (
                            SELECT datetime_adopted
                            FROM payments
                            WHERE paid=1
                        ) as smth
                        GROUP BY period
                        HAVING DATE(datetime_adopted) > DATE(DATE_SUB(CURDATE(), INTERVAL 12 MONTH))
                        ORDER BY YEAR(datetime_adopted) ASC, MONTH(datetime_adopted) ASC";
                $result[] = $this->db->GetArray($sql);
                return $this->clear_array($result);
                break;
            }
            case 'week': {
                $sql = "SELECT CONCAT(YEAR(datetime_adopted - WEEKDAY(datetime_adopted)), '-', WEEK(datetime_adopted, 3)) as period, COUNT(*) as cnt, datetime_adopted
                        FROM (
                            SELECT datetime_adopted, COUNT(*) as cnt
                            FROM payments
                            WHERE paid=1
                            GROUP BY user_id
                            HAVING cnt=1
                        ) as smth
                        GROUP BY period
                        HAVING YEARWEEK(datetime_adopted) > YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 20 WEEK),3)
                        ORDER BY datetime_adopted";
                $result[] = $this->db->GetArray($sql);
                $sql = "SELECT CONCAT(YEAR(datetime_adopted - WEEKDAY(datetime_adopted)), '-', WEEK(datetime_adopted, 3)) as period, COUNT(*) as cnt, datetime_adopted
                        FROM (
                            SELECT datetime_adopted
                            FROM payments
                            WHERE paid=1
                        ) as smth
                        GROUP BY period
                        HAVING YEARWEEK(datetime_adopted) > YEARWEEK(DATE_SUB(CURDATE(), INTERVAL 20 WEEK),3)
                        ORDER BY datetime_adopted";
                $result[] = $this->db->GetArray($sql);
                return $this->clear_array($result);
                break;
            }
            default: {
                return array();
                break;
            }

        }
    }

    public function getMoney($period = 'year') {
        if ($period == 'year') {
            $x = 365+date('d',time());
            $sql = "SELECT CONCAT(YEAR(datetime_adopted), '-', MONTH(datetime_adopted)) as period, SUM(summa) as summ, datetime_adopted
                    FROM (
                        SELECT datetime_adopted, summa
                        FROM payments
                        WHERE paid=1 AND type_payment='electron'
                    ) as smth
                    GROUP BY period
                    HAVING DATE(datetime_adopted) > DATE(DATE_SUB(CURDATE(), INTERVAL " . $x . " DAY))
                    ORDER BY YEAR(datetime_adopted) ASC, MONTH(datetime_adopted) ASC";
            $result[] = $this->db->GetArray($sql);

            $sql = "SELECT CONCAT(YEAR(datetime_adopted), '-', MONTH(datetime_adopted)) as period, SUM(summa) as summ, datetime_adopted
                    FROM (
                        SELECT datetime_adopted, summa
                        FROM payments
                        WHERE paid=1 AND type_payment='cashless'
                    ) as smth
                    GROUP BY period
                    HAVING DATE(datetime_adopted) > DATE(DATE_SUB(CURDATE(), INTERVAL " . $x . " DAY))
                    ORDER BY YEAR(datetime_adopted) ASC, MONTH(datetime_adopted) ASC";
            $result[] = $this->db->GetArray($sql);

            $sql = "SELECT CONCAT(YEAR(datetime_adopted), '-', MONTH(datetime_adopted)) as period, SUM(summa) as summ, datetime_adopted
                    FROM (
                        SELECT datetime_adopted, summa
                        FROM payments
                        WHERE paid=1
                    ) as smth
                    GROUP BY period
                    HAVING DATE(datetime_adopted) > DATE(DATE_SUB(CURDATE(), INTERVAL " . $x . " DAY))
                    ORDER BY YEAR(datetime_adopted) ASC, MONTH(datetime_adopted) ASC";
            $result[] = $this->db->GetArray($sql);
        } elseif ($period == 'week') {
            $sql = "SELECT CONCAT(YEAR(datetime_adopted - WEEKDAY(datetime_adopted)), '-', WEEK(datetime_adopted, 3)) as period, SUM(summa) as summ, datetime_adopted
                    FROM (
                        SELECT datetime_adopted, summa
                        FROM payments
                        WHERE paid=1 AND type_payment='electron'
                    ) as smth
                    GROUP BY period
                    HAVING DATE(datetime_adopted) > DATE(DATE_SUB(CURDATE(), INTERVAL 20 WEEK))
                    ORDER BY datetime_adopted";
            $result[] = $this->db->GetArray($sql);

            $sql = "SELECT CONCAT(YEAR(datetime_adopted - WEEKDAY(datetime_adopted)), '-', WEEK(datetime_adopted, 3)) as period, SUM(summa) as summ, datetime_adopted
                    FROM (
                        SELECT datetime_adopted, summa
                        FROM payments
                        WHERE paid=1 AND type_payment='cashless'
                    ) as smth
                    GROUP BY period
                    HAVING DATE(datetime_adopted) > DATE(DATE_SUB(CURDATE(), INTERVAL 20 WEEK))
                    ORDER BY datetime_adopted";
            $result[] = $this->db->GetArray($sql);

            $sql = "SELECT CONCAT(YEAR(datetime_adopted - WEEKDAY(datetime_adopted)), '-', WEEK(datetime_adopted, 3)) as period, SUM(summa) as summ, datetime_adopted
                    FROM (
                        SELECT datetime_adopted, summa
                        FROM payments
                        WHERE paid=1
                    ) as smth
                    GROUP BY period
                    HAVING DATE(datetime_adopted) > DATE(DATE_SUB(CURDATE(), INTERVAL 20 WEEK))
                    ORDER BY datetime_adopted";
            $result[] = $this->db->GetArray($sql);

        }

        return $this->clear_array($result);
    }

    public function getNewRegs($period = 'year') {
        if ($period == 'week') {
            $sql = "SELECT CONCAT(YEAR(timestamp - WEEKDAY(timestamp)), '-', WEEK(timestamp, 3)) as period, COUNT(*) as cnt, timestamp
                    FROM (
                        SELECT timestamp
                        FROM users
                    ) as smth
                    GROUP BY period
                    HAVING DATE(timestamp) > DATE(DATE_SUB(CURDATE(), INTERVAL 20 WEEK))
                    ORDER BY timestamp";
            $result[] = $this->db->GetArray($sql);
        } elseif ($period == 'year') {
            $sql = "SELECT CONCAT(YEAR(timestamp), '-', MONTH(timestamp)) as period, COUNT(*) as cnt, timestamp
                    FROM (
                        SELECT timestamp
                        FROM users
                    ) as smth
                    GROUP BY period
                    HAVING DATE(timestamp) > DATE(DATE_SUB(CURDATE(), INTERVAL 20 MONTH))
                    ORDER BY YEAR(timestamp) ASC, MONTH(timestamp) ASC";
            $result[] = $this->db->GetArray($sql);

        }
        return $this->clear_array($result);
    }

    public function getNewItems($period = 'year') {
        if ($period == 'year') {
            $sql = "SELECT CONCAT(YEAR(date), '-', MONTH(date)) as period, COUNT(*) as cnt, date
                    FROM item
                    GROUP BY period
                    HAVING DATE(date) > DATE(DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AND DATE(date) <= CURDATE()
                    ORDER BY YEAR(date) ASC, MONTH(date) ASC";
            $result[] = $this->db_tenders->GetArray($sql);
        } elseif ($period == 'week') {
            $sql = "SELECT CONCAT(YEAR(date - WEEKDAY(date)), '-', WEEK(date, 3)) as period, COUNT(*) as cnt, date
                    FROM item
                    GROUP BY period
                    HAVING DATE(date) > DATE(DATE_SUB(CURDATE(), INTERVAL 20 WEEK)) AND DATE(date) <= CURDATE()
                    ORDER BY date";
            $result[] = $this->db_tenders->GetArray($sql);
        }
        return $this->clear_array($result);
    }

    public function getPriceAvg($type, $id) {
        $sql = "SELECT CONCAT(YEAR(date_publication - WEEKDAY(date_publication)), '-', WEEK(date_publication, 3)) AS period, ROUND( SUM( price ) / COUNT( price ) ) AS avg
                FROM (
                    SELECT price, date_publication
                    FROM item
                    WHERE $type=$id
                    AND date_publication IS NOT NULL
                    ) AS smth
                GROUP BY period
                HAVING period > YEARWEEK( DATE_SUB( CURDATE( ) , INTERVAL 20 WEEK ) ) ";
        $result[] = $this->db_tenders->GetArray($sql);
        return $this->clear_array($result);
    }

    function check_array($array) {
        $flag = false;
        foreach ($array as $a2) {
            if (count($a2) > 0) {
                $flag = true;
                break;
            }
        }
        return $flag;
    }
    
    function fill_array($array, $from, $to, $group) {
        $result = array();
        
        while ($from <= $to) {
            if ($group == 1) {
                $from_h = date('d.m.Y', $from);
            } else if ($group == 2) {
                $from_h = date('W.Y', $from);
            } else {
                $from_h = date('m.Y', $from);
            }
            
            $flag = FALSE;
            foreach ($array as $k1 => $res) {
                if ($res['0'] == $from_h) {
                    $result[] = $res;
                    $flag = TRUE;
                    break;
                }
            }
            if ($flag == FALSE) {
                $result[] = array('0' => $from_h, '1' => 0, '2' => $from_h);
            }
            
            if ($group == 1) {
                $from += 86400;
            } else if ($group == 2) {
                $from += 604800;
            } else {
                $from = mktime(0, 0, 0, date('m', $from)+1, date('d', $from), date('Y', $from));
            }
        }
        
        //print_r($result);
        return $result;
    }
    
    function clear_array($array, $step = 0) {
        if (!is_array($array)) {
            return array();
        }
        
        //print_r($array);
        
        $result = array();
        foreach ($array as $k1 => $a2) {
            $subresult = array();
            foreach ($a2 as $k2 =>$element) {
                if (!is_array($element)) {
                    continue;
                }
                if ($step != 0) {
                    if ($step > (int)$k1) {
                        if (isset($array[$k1+$step][$k2])) {
                            $subresult[$element[0].'-'.$array[$k1+$step][$k2][0]]['value'] = $element[1];
                            if ($k1 != 0 && $k1 != $step && $array[0][$k2][1] != 0) {
                                $subresult[$element[0].'-'.$array[$k1+$step][$k2][0]]['label'] = $element[1].' ('.sprintf('%.1f', ($element[1] / $array[0][$k2][1])*100).'%)';
                            } else {
                                $subresult[$element[0].'-'.$array[$k1+$step][$k2][0]]['label'] = $element[1];
                            }
                        }
                    } else {
                        if (isset($array[$k1-$step][$k2])) {
                            $subresult[$array[$k1-$step][$k2][0].'-'.$element[0]]['value'] = $element[1];
                            if ($k1 != 0 && $k1 != $step && $array[$k1-$step][$k2][1] != 0) {
                                $subresult[$array[$k1-$step][$k2][0] .'-'. $element[0]]['label'] = $element[1].' ('.sprintf('%.1f', ($element[1] / $array[$step][$k2][1])*100).'%)';
                            } else {
                                $subresult[$array[$k1-$step][$k2][0] .'-'. $element[0]]['label'] = $element[1];
                            }
                        }
                    }
                } else {
                    //$subresult[$element[0]] = $element[1];
                    $subresult[$element[0]]['value'] = $element[1];
                    if ($k1 != 0 && $array[0][$k2][1] != 0) {
                        $subresult[$element[0]]['label'] = $element[1].' ('.sprintf('%.1f', ($element[1] / $array[0][$k2][1])*100).'%)';
                    } else {
                        $subresult[$element[0]]['label'] = $element[1];
                    }
                }
            }
            $result[] = $subresult;
        }
        return $result;
    }
    
    public function getItemsByModer($period) {
        if ($period == 'year') {
            $sql = "SELECT CONCAT(YEAR(date), '-', MONTH(date)) as period, COUNT(*) as cnt, date
                    FROM item
                    WHERE site_id=990001
                    GROUP BY period
                    HAVING DATE(date) > DATE(DATE_SUB(CURDATE(), INTERVAL 12 MONTH)) AND DATE(date) <= CURDATE()
                    ORDER BY YEAR(date) ASC, MONTH(date) ASC";
            $result[] = $this->db_tenders->GetArray($sql);
        } elseif ($period == 'week') {
            $sql = "SELECT CONCAT(YEAR(date - WEEKDAY(date)), '-', WEEK(date, 3)) as period, COUNT(*) as cnt, date
                    FROM item
                    WHERE site_id=990001
                    GROUP BY period
                    HAVING DATE(date) > DATE(DATE_SUB(CURDATE(), INTERVAL 20 WEEK)) AND DATE(date) <= CURDATE()
                    ORDER BY date";
            $result[] = $this->db_tenders->GetArray($sql);
        }
        return $this->clear_array($result);
    }

    function getSites() {
        $sql = "SELECT site_id, cnt FROM away";
        $t = $this->db->GetArray($sql);
        echo "\n";
        foreach ($t as $el) {
            $result[$this->db_tenders->GetOne("SELECT url FROM site WHERE id=?", $el['site_id'])] = $el['cnt'];
        }
        //return $this->clear_array($result);
        $result[] = $result;
        return $result;
    }

}